﻿Imports System.Xml
Imports System.IO
Imports System.Text
Imports SQLtoASPX.Globals
Imports SQLtoASPX.Support
Imports System.Collections.Generic
Imports System.Data.SqlClient

Public Class WCFClassTabPage
    Inherits System.Windows.Forms.TabPage

    Friend WithEvents cmdGenerate As System.Windows.Forms.Button
    Friend WithEvents Label20 As System.Windows.Forms.Label
    Friend WithEvents txtPrivateMemberPrefix As System.Windows.Forms.TextBox
    Friend WithEvents Label19 As System.Windows.Forms.Label
    Friend WithEvents cboClassGenerationStyle As System.Windows.Forms.ComboBox
    Friend WithEvents txtPrefix As System.Windows.Forms.TextBox
    Friend WithEvents Label16 As System.Windows.Forms.Label


    Public DatabaseName As String = ""
    Public dt As DataTable = Nothing
    Public gTableName As String = ""
    Public ServerName As String = ""

    Public DatabaseServer As DatabaseServer = Nothing

    Private mTreeNode As TreeNode
    Public Property TreeNode() As TreeNode
        Get
            Return mTreeNode
        End Get
        Set(ByVal value As TreeNode)
            mTreeNode = value
        End Set
    End Property

    Public Sub New()

        'Me.Controls.Add(New DataGridView)
        InitializeComponent()
        cboClassGenerationStyle.Items.Add("Public")
        cboClassGenerationStyle.Items.Add("Property")
        cboClassGenerationStyle.SelectedIndex = 1
    End Sub


    Private Sub InitializeComponent()
        Me.Label20 = New System.Windows.Forms.Label
        Me.txtPrivateMemberPrefix = New System.Windows.Forms.TextBox
        Me.Label19 = New System.Windows.Forms.Label
        Me.cboClassGenerationStyle = New System.Windows.Forms.ComboBox
        Me.txtPrefix = New System.Windows.Forms.TextBox
        Me.Label16 = New System.Windows.Forms.Label
        Me.cmdGenerate = New System.Windows.Forms.Button

        Me.SuspendLayout()


        '
        'cmdGenerate
        '
        Me.cmdGenerate.Anchor = System.Windows.Forms.AnchorStyles.Top
        Me.cmdGenerate.Location = New System.Drawing.Point(70, 0)
        Me.cmdGenerate.Name = "cmdGenerate"
        Me.cmdGenerate.Size = New System.Drawing.Size(75, 23)
        Me.cmdGenerate.TabIndex = 0
        Me.cmdGenerate.Text = "Generate"
        Me.Controls.Add(cmdGenerate)


        'Label20
        '
        Me.Label20.AutoSize = True
        Me.Label20.Location = New System.Drawing.Point(12, 55)
        Me.Label20.Name = "Label20"
        Me.Label20.Size = New System.Drawing.Size(107, 13)
        Me.Label20.TabIndex = 46
        Me.Label20.Text = "Private Member Pefix"
        '
        'txtPrivateMemberPrefix
        '
        Me.txtPrivateMemberPrefix.Location = New System.Drawing.Point(125, 55)
        Me.txtPrivateMemberPrefix.Name = "txtPrivateMemberPrefix"
        Me.txtPrivateMemberPrefix.Size = New System.Drawing.Size(100, 20)
        Me.txtPrivateMemberPrefix.TabIndex = 45
        Me.txtPrivateMemberPrefix.Text = "m"
        '
        'Label19
        '
        Me.Label19.AutoSize = True
        Me.Label19.Location = New System.Drawing.Point(12, 31)
        Me.Label19.Name = "Label19"
        Me.Label19.Size = New System.Drawing.Size(72, 13)
        Me.Label19.TabIndex = 44
        Me.Label19.Text = "Property Style"
        '
        'cboClassGenerationStyle
        '
        Me.cboClassGenerationStyle.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
        Me.cboClassGenerationStyle.FormattingEnabled = True
        Me.cboClassGenerationStyle.Location = New System.Drawing.Point(125, 31)
        Me.cboClassGenerationStyle.Name = "cboClassGenerationStyle"
        Me.cboClassGenerationStyle.Size = New System.Drawing.Size(281, 21)
        Me.cboClassGenerationStyle.TabIndex = 43
        '
        'txtPrefix
        '
        Me.txtPrefix.Location = New System.Drawing.Point(125, 7)
        Me.txtPrefix.Name = "txtPrefix"
        Me.txtPrefix.Size = New System.Drawing.Size(100, 20)
        Me.txtPrefix.TabIndex = 42
        Me.txtPrefix.Text = "C"
        '
        'Label16
        '
        Me.Label16.AutoSize = True
        Me.Label16.Location = New System.Drawing.Point(12, 7)
        Me.Label16.Name = "Label16"
        Me.Label16.Size = New System.Drawing.Size(61, 13)
        Me.Label16.TabIndex = 41
        Me.Label16.Text = "Class Prefix"
        Me.Label16.TextAlign = System.Drawing.ContentAlignment.TopCenter

        Me.Controls.Add(Me.Label20)
        Me.Controls.Add(Me.txtPrivateMemberPrefix)
        Me.Controls.Add(Me.Label19)
        Me.Controls.Add(Me.cboClassGenerationStyle)
        Me.Controls.Add(Me.txtPrefix)
        Me.Controls.Add(Me.Label16)

        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub

    Private Sub SetComboBoxValue(ByVal cbo As ComboBox, ByVal Value As String)
        For i As Long = 0 To cbo.Items.Count - 1
            If cbo.Items(i)(1).ToString.ToUpper = Value.ToUpper Then
                cbo.SelectedIndex = i
                Exit Sub
            End If
        Next
    End Sub
    Private Sub SetComboBoxValue2(ByVal cbo As ComboBox, ByVal Value As String)
        For i As Long = 0 To cbo.Items.Count - 1
            If cbo.Items(i).ToString.ToUpper = Value.ToUpper Then
                cbo.SelectedIndex = i
                Exit Sub
            End If
        Next
    End Sub

    Private Sub cmdGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerate.Click
        If MasterForm.txtPath.Text.Length = 0 Then
            MsgBox("Unable to generate please set the output directory.")
            Logit("Unable to generate please set the output directory.")
            MasterForm.TabControl1.SelectedIndex = 0
            MasterForm.txtPath.Focus()
            Exit Sub
        End If
        Logit("Generation Started at " & Now.ToString)

        LoadGrid(TreeNode)

        Dim s As String = ""

        Dim IdentityColumnName As String = ""
        Dim IdentityColumnType As String = "long"

        For Each row As DataRow In dt.Rows
            If row("IsIdentity") Then
                IdentityColumnName = row("ColumnName")
            End If
        Next

        If IdentityColumnName.Length = 0 Then
            MsgBox("Table : " & gTableName & " has no autonumber column")
            Exit Sub
        End If

        s = s & "Imports System.ServiceModel" & vbCrLf
        s = s & "Imports System.Runtime.Serialization" & vbCrLf
        s = s & vbCrLf
        s = s & "<DataContract()> _ " & vbCrLf
        s = s & "Public Class " & txtPrefix.Text & gTableName & vbCrLf
        For Each row As DataRow In dt.Rows
            s = s & "" & vbCrLf
            If row("AllowNulls") Then
                s = s & "   Private " & txtPrivateMemberPrefix.Text & "IsNull" & row("ColumnName") & " As Boolean" & vbCrLf
                s = s & "   <DataMember()> _ " & vbCrLf
                s = s & "   Property IsNull" & row("ColumnName") & "() As Boolean" & vbCrLf
                s = s & "      Get" & vbCrLf
                s = s & "         Return " & txtPrivateMemberPrefix.Text & "IsNull" & row("ColumnName") & "" & vbCrLf
                s = s & "      End Get" & vbCrLf
                s = s & "      Set(ByVal value As Boolean)" & vbCrLf
                s = s & "         " & txtPrivateMemberPrefix.Text & "IsNull" & row("ColumnName") & " = value" & vbCrLf
                s = s & "      End Set" & vbCrLf
                s = s & "   End Property" & vbCrLf
                s = s & vbCrLf

                's = s & "   Public Property IsNull" & row("ColumnName") & " as Boolean" & vbCrLf
            End If
            s = s & "   Private " & txtPrivateMemberPrefix.Text & row("ColumnName") & " As " & SQLDataTypeToGenType(row("DataType")) & vbCrLf
            s = s & "   <DataMember()> _ " & vbCrLf
            s = s & "   Property " & row("ColumnName") & "() As " & SQLDataTypeToGenType(row("DataType")) & vbCrLf
            s = s & "      Get" & vbCrLf
            s = s & "         Return " & txtPrivateMemberPrefix.Text & row("ColumnName") & "" & vbCrLf
            s = s & "      End Get" & vbCrLf
            s = s & "      Set(ByVal value As " & SQLDataTypeToGenType(row("DataType")) & ")" & vbCrLf
            s = s & "         " & txtPrivateMemberPrefix.Text & row("ColumnName") & " = value" & vbCrLf
            s = s & "      End Set" & vbCrLf
            s = s & "   End Property" & vbCrLf

        Next

        s = s & "End Class" & vbCrLf
        s = s & "   <OperationContract()> _ " & vbCrLf
        s = s & "   Public Function Get" & gTableName & "By" & IdentityColumnName & "(ByVal " & IdentityColumnName & " As " & IdentityColumnType & ") as C" & gTableName & vbCrLf
        s = s & "      Dim " & gTableName & " as New C" & gTableName & vbCrLf
        s = s & "" & vbCrLf
        s = s & "      Dim Connection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings(""" & MasterForm.txtConnectionString.Text & """).ConnectionString)" & vbCrLf
        s = s & "      Connection.Open()" & vbCrLf
        s = s & "" & vbCrLf
        s = s & "      Dim Command As New SqlCommand(""select * from " & gTableName & " where " & IdentityColumnName & " = @" & IdentityColumnName & """, Connection)" & vbCrLf
        s = s & "      Command.Parameters.AddWithValue(""@" & IdentityColumnName & """, " & IdentityColumnName & ")" & vbCrLf
        s = s & "" & vbCrLf
        s = s & "      Dim Reader As SqlDataReader = Command.ExecuteReader" & vbCrLf
        s = s & "      If Reader.Read Then" & vbCrLf
        For Each row As DataRow In dt.Rows
            If row("AllowNulls") Then
                s = s & "         If IsDBNull(Reader(""" & row("ColumnName") & """)) Then" & vbCrLf
                s = s & "            " & gTableName & ".IsNull" & row("ColumnName") & " = True" & vbCrLf
                s = s & "         else" & vbCrLf
                s = s & "            " & gTableName & "." & row("ColumnName") & " = Reader(""" & row("ColumnName") & """)" & vbCrLf
                s = s & "         end if " & vbCrLf
            Else
                s = s & "         " & gTableName & "." & row("ColumnName") & " = Reader(""" & row("ColumnName") & """)" & vbCrLf
            End If
        Next

        s = s & "      End If" & vbCrLf
        s = s & "      Reader.Close()" & vbCrLf
        s = s & "      Connection.Close()" & vbCrLf
        s = s & "      Return " & gTableName & vbCrLf
        s = s & "   End Function" & vbCrLf
        s = s & vbCrLf
        s = s & "   <OperationContract()> _ " & vbCrLf
        s = s & "   Public Function Get" & gTableName & "sByWhereClause(ByVal WhereClause As String, ByVal OrderByClause As String) as C" & gTableName & "()" & vbCrLf
        s = s & "" & vbCrLf
        s = s & "      Dim Connection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings(""" & MasterForm.txtConnectionString.Text & """).ConnectionString)" & vbCrLf
        s = s & "      Connection.Open()" & vbCrLf
        s = s & "" & vbCrLf
        s = s & "      Dim SQL as string = ""select * from " & gTableName & """" & vbCrLf
        s = s & "      If len(WhereClause) > 0 then " & vbCrLf
        s = s & "         SQL = SQL & "" where "" & WhereClause " & vbCrLf
        s = s & "      End If " & vbCrLf
        s = s & "      If len(OrderByClause) > 0 then " & vbCrLf
        s = s & "         SQL = SQL & "" order by  "" & OrderByClause " & vbCrLf
        s = s & "      End If " & vbCrLf
        s = s & "      Dim Command As New SqlCommand(SQL, Connection)" & vbCrLf
        s = s & "" & vbCrLf
        s = s & "      Dim Reader As SqlDataReader = Command.ExecuteReader" & vbCrLf
        s = s & "      Dim lst As New System.Collections.Generic.List(Of C" & gTableName & ")" & vbCrLf
        s = s & "      Do While Reader.Read " & vbCrLf
        s = s & "         Dim " & gTableName & " as New C" & gTableName & vbCrLf


        'Private Function GetSpaces() As Space()

        '    Return lst.ToArray()

        'End Function
        For Each row As DataRow In dt.Rows
            If row("AllowNulls") Then
                s = s & "         If IsDBNull(Reader(""" & row("ColumnName") & """)) Then" & vbCrLf
                s = s & "            " & gTableName & ".IsNull" & row("ColumnName") & " = True" & vbCrLf
                s = s & "         else" & vbCrLf
                s = s & "            " & gTableName & "." & row("ColumnName") & " = Reader(""" & row("ColumnName") & """)" & vbCrLf
                s = s & "         end if " & vbCrLf
            Else
                s = s & "         " & gTableName & "." & row("ColumnName") & " = Reader(""" & row("ColumnName") & """)" & vbCrLf
            End If
        Next
        s = s & "         lst.Add(" & gTableName & ")" & vbCrLf
        s = s & "      Loop" & vbCrLf
        s = s & "      Reader.Close()" & vbCrLf
        s = s & "      Connection.Close()" & vbCrLf
        s = s & "      Return lst.ToArray()" & vbCrLf
        s = s & "   End Function" & vbCrLf

        Dim VBText As String = s
        s = ""
        s = s & vbCrLf

        Dim Filename As String = MasterForm.txtPath.Text
        If Strings.Right(Filename, 1) = "\" Then
        Else
            Filename = Filename & "\"
        End If

        Filename = Filename & txtPrefix.Text & gTableName & ".vb"
        Dim fs As New System.IO.FileStream(Filename, FileMode.Create)
        Dim AE As New ASCIIEncoding()
        Dim out As Byte() = AE.GetBytes(VBText)
        fs.Write(out, 0, out.Length)
        fs.Close()


        Logit("Class " & txtPrefix.Text & gTableName & " to " & Filename)

        Logit("Generation Complete " & Now.ToString)
        MsgBox("Generation Complete")
    End Sub

    Private Function GetDataTable() As DataTable
        Dim dt As New DataTable
        Dim DataColumn As New DataColumn
        DataColumn.ColumnName = "ColumnName"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "DataType"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "ShowInMasterView"
        DataColumn.DataType = GetType(Boolean)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "ShowInDetailView"
        DataColumn.DataType = GetType(Boolean)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "SearchType"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "IsGroupColumn"
        DataColumn.DataType = GetType(Boolean)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "AllowNulls"
        DataColumn.DataType = GetType(Boolean)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "ColumnSize"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "IsIdentity"
        DataColumn.DataType = GetType(Boolean)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "ModifyLookup"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "LinkTableName"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "LinkTableKey"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)

        DataColumn = New DataColumn
        DataColumn.ColumnName = "LinkTableValue"
        DataColumn.DataType = GetType(String)
        dt.Columns.Add(DataColumn)
        Return dt
    End Function


    Public Sub LoadGrid(ByVal e As TreeNode)
        TreeNode = e

        Dim CurrentNode As TreeNode = e
        ' Dim CurrentTable As SQLtoASPX.Table = CurrentNode.Parent.Tag

        dt = GetDataTable()
        Dim ConnectionString As String = GetConnectionStringFromTreeNode(CurrentNode)
        DatabaseServer = GetDatabaseServerFromTreeNode(CurrentNode)
        Dim Connection As New SqlConnection(ConnectionString)
        Connection.Open()
        'Dim DB As SQLtoASPX.Database = CurrentNode.Parent.Tag
        DatabaseName = CurrentNode.Parent.Text
        ServerName = CurrentNode.Parent.Parent.Text

        gTableName = CurrentNode.Text
        Dim sql As String = "select * from " & DatabaseName & ".dbo.syscolumns c inner join " & DatabaseName & ".dbo.sysObjects o on o.id = c.id where o.Name = '" & gTableName & "'"
        Dim Command As New SqlCommand(sql, Connection)
        Debug.Print(sql)
        Dim Reader As SqlDataReader = Nothing
        Try
            Reader = Command.ExecuteReader
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        End Try
        Do While Reader.Read
            Dim column As New Column(Reader)
            Dim dr As DataRow = dt.NewRow

            dr("ColumnName") = column.Name
            dr("DataType") = column.GetColumnTypeName
            dr("ShowInMasterView") = column.ShowInMasterView
            dr("ShowInDetailView") = column.ShowInDetailView
            dr("SearchType") = column.SearchType
            dr("IsGroupColumn") = column.IsGroupColumn
            dr("AllowNulls") = column.AllowNulls
            dr("ColumnSize") = column.Length
            dr("IsIdentity") = column.IsIdentity
            dr("ModifyLookup") = "..."
            dt.Rows.Add(dr)
        Loop
        Reader.Close()
        Connection.Close()


    End Sub

    Private Sub Logit(ByVal s As String)
        MasterForm.txtOutput.Text = s & vbCrLf & MasterForm.txtOutput.Text
    End Sub

    Private Function GetSpaces() As Space()
        Dim lst As New System.Collections.Generic.List(Of Space)
        Dim s As New Space
        lst.Add(s)
        Return lst.ToArray()

    End Function
End Class
